package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Fine;

public class FineDao {

	public FineDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	//String pass = "webclass";

	// fine 값 종류 불러오기
	public ArrayList<Fine> getAllFine() throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Fine> fineList = new ArrayList<Fine>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con.prepareStatement("select * from fine");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int fineid = rs.getInt("fineid");
				int fine = rs.getInt("fine");

				fineList.add(new Fine(fineid, fine));
			}

			return fineList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return fineList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// fine id에 맞는 fine value 가져오기
	public int getFineValue(int fineId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int fine = 0;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select fine from fine where fineId=?");
			pstmt.setInt(1, fineId);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				fine = rs.getInt("fine");

			}

			return fine;

		} catch (Exception ex) {
			ex.printStackTrace();
			return fine;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// sid, uid에 맞는 fine value 가져오기
	public int getFineValueBysid(int sid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int fineValue = 0;
		try {

			con = DriverManager.getConnection(url, user, pass);

			pstmt = con
					.prepareStatement("select fine from fine,study where studyId=? and fineId=fid");
			pstmt.setInt(1, sid);
			// pstmt.setInt(2, uid);
			pstmt.executeQuery();
			if (rs.next()) {
				fineValue = rs.getInt("fine");
			}

			return fineValue;

		} catch (Exception ex) {
			ex.printStackTrace();
			return fineValue;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}
